package com.example.sqlitedrugs;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {
	private static final String DB_NAME = "MyDataBase.db";
	private static final int vs = 1;
	private SQLiteDatabase database;
	public static final String TB_DRUGS = "Drugs";

	// init ten cac cot trong table
	public static final String CL_id = "_id";
	public static final String CL_Name = "Name";
	public static final String CL_Verb = "Verb";

	public MySQLiteOpenHelper(Context context) {
		super(context, DB_NAME, null, vs);
		// TODO Auto-generated constructor stub
		database = this.getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sqlCreatDrugs = "CREATE TABLE " + TB_DRUGS + "(" + CL_id
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + CL_Name + " TEXT, "
				+ CL_Verb + " TEXT)";
		// cac ban nen log cac cau sql ra va chay thu tren sqliteadmin de phat
		// hien ra loi
		Log.d("debug", sqlCreatDrugs);
		db.execSQL(sqlCreatDrugs);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TB_DRUGS);
		onCreate(db);

	}

	public void INSERT_Drugs(Drugs mDrugs) {
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(CL_Name, mDrugs.getName());
		values.put(CL_Verb, mDrugs.getVerb());
		database.insert(TB_DRUGS, null, values);
		database.close();
	}

	public void DELETE_Drugs(int id) {
		SQLiteDatabase database = this.getWritableDatabase();

		database.delete(TB_DRUGS, CL_id + " = ?",
				new String[] { String.valueOf(Drugs.get_id()) });
		database.close();
	}

	public Drugs getDrugs(int id) {
		// cap quyen doc CSDL cho bien database
		SQLiteDatabase database = this.getReadableDatabase();

		// gan cau lenh SQL vao bien selectQuerry
		String selectQuerry = "SELECT * FROM " + TB_DRUGS + " WHERE " + CL_id
				+ " = " + id;

		// Log ra selectQuerry
		Log.d("debug", selectQuerry);

		// doi tuong luu cac hang cua bang truy van
		Cursor c = database.rawQuery(selectQuerry, null);

		// chuyen con tro den dong dau tien neu du lieu tra ve tu CSDL khong
		// phai null
		if (c != null) {
			c.moveToFirst();
		}

		// dong goi thong tin vao 1 doi tuong drugs
		Drugs drugs = new Drugs();
		drugs.set_id(c.getInt(c.getColumnIndex(CL_id)));
		drugs.setName(c.getString(c.getColumnIndex(CL_Name)));
		drugs.setVerb(c.getString(c.getColumnIndex(CL_Verb)));

		// tra ve 1 drugs
		return drugs;
	}

	// lay thong tin tat ca drugs ra tu CSDL
	public ArrayList<Drugs> getAlldrugs() {
		ArrayList<Drugs> arrDrugs = new ArrayList<Drugs>();

		SQLiteDatabase database = this.getReadableDatabase();
		String selectQuerry = "SELECT * FROM " + TB_DRUGS;

		Log.d("debug", selectQuerry);

		Cursor c = database.rawQuery(selectQuerry, null);

		if (c != null) {
			c.moveToFirst();

			do {
				// dong goi thong tin vao 1 doi tuong drugs
				Drugs drugs = new Drugs();

				drugs.set_id(c.getInt(c.getColumnIndex(CL_id)));
				drugs.setName(c.getString(c.getColumnIndex(CL_Name)));
				drugs.setVerb(c.getString(c.getColumnIndex(CL_Verb)));

				arrDrugs.add(drugs);
			} while (c.moveToNext()); // chuyen toi dong tiep theo
		}

		// tra ve danh sach cac drugs
		return arrDrugs;
	}

	public int UPDATE_Drugs(Drugs mDrugs, int id) {
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(CL_Name, mDrugs.getName());
		values.put(CL_Verb, mDrugs.getVerb());
		return database.update(TB_DRUGS, values, CL_id + " = ?",
				new String[] { String.valueOf(Drugs.get_id()) });

	}

	public Cursor SELECT_ALL__Drugs() {
		SQLiteDatabase database = this.getWritableDatabase();
		return database.query(TB_DRUGS, null, null, null, null, null, null);
	}

	public Cursor SELECTSQL(String sql) {
		SQLiteDatabase database = this.getWritableDatabase();
		return database.rawQuery(sql, null);
	}
	public int getDrugsCount(){
		String countQuery = "SELECT  * FROM " + TB_DRUGS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		// return count
		return cursor.getCount();
		
	}

	// phuong thuc nay de dong DB khi khong su dung
	public void CloseBD() {
		SQLiteDatabase database = this.getWritableDatabase();
		if (database != null && database.isOpen())
			database.close();
	}
}
